package csc.fresher.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.SystemUserDao;
import csc.fresher.entity.SystemRecordLogin;
import csc.fresher.entity.SystemUser;
import csc.fresher.entity.SystemUserState;
import csc.fresher.utils.DBUtils;

@Repository
public class SystemUserDaoImpl implements SystemUserDao {

	@Autowired
	DataSource dataSource;

	public Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection(
					"jdbc:mysql://20.203.182.23:3306/finalProject", "root",
					"root");
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	private static final Logger logger = Logger
			.getLogger(SavingAccountDaoImpl.class);


	public static long generateKey;

	@Override
	public long createSystemUser(SystemUser systemUser) {
		String query = "INSERT INTO `SystemUser` (`SystemUserID`,`UserName`,`PassWord`,`UserStatus`,`IDCardNumber`,`FirstName`, `LastName`, `MidName`, `PhoneNumber`, `PhoneNumber_2`, `Address`, `Address_2`, `Email`, `Email_2`, `CreatedDate`, `LastUpdate`, `CreatedBy`, `ApprovedBy`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setLong(1, systemUser.getSystemUserID());
			preparedStatement.setString(2, systemUser.getUserName());
			preparedStatement.setString(3, systemUser.getPassWord());
			preparedStatement.setString(4, SystemUserState.NEW.getState());
			preparedStatement.setString(5, systemUser.getIdCardNumber());
			preparedStatement.setString(6, systemUser.getFirstName());
			preparedStatement.setString(7, systemUser.getLastName());
			preparedStatement.setString(8, systemUser.getMidName());
			preparedStatement.setString(9, systemUser.getPhoneNumber());
			preparedStatement.setString(10, systemUser.getPhoneNumber2());
			preparedStatement.setString(11, systemUser.getAddress());
			preparedStatement.setString(12, systemUser.getAddress2());
			preparedStatement.setString(13, systemUser.getEmail());
			preparedStatement.setString(14, systemUser.getEmail2());
			preparedStatement.setTimestamp(15, systemUser.getCreatedDate());
			preparedStatement.setTimestamp(16, systemUser.getLastUpdate());
			preparedStatement.setLong(17, systemUser.getSystemUserCreateID());
			preparedStatement.setLong(18, systemUser.getSystemUserActiveID());

			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				return rs.getLong(1);
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public long updateSystemUser(SystemUser systemUser) {
		String query = "UPDATE `SystemUser` "
				+ " SET  `UserName`=?, `Password`=?, `UserStatus`=?, `IDCardNumber`=?"
				+ ", `FirstName`=?, `LastName`=?, `MidName`=?, `PhoneNumber`=?"
				+ ", `PhoneNumber_2`=?, `Address`=?, `Address_2`=?, `Email`=?"
				+ ", `Email_2`=?, `CreatedDate`=?, `LastUpdate`=?, `CreatedBy`=?, `ApprovedBy`=? "
				+ "WHERE `SystemUserID`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, systemUser.getUserName());
			preparedStatement.setString(2, systemUser.getPassWord());
			preparedStatement.setString(3, SystemUserState.ACTIVE.getState());
			preparedStatement.setString(4, systemUser.getIdCardNumber());
			preparedStatement.setString(5, systemUser.getFirstName());
			preparedStatement.setString(6, systemUser.getLastName());
			preparedStatement.setString(7, systemUser.getMidName());
			preparedStatement.setString(8, systemUser.getPhoneNumber());
			preparedStatement.setString(9, systemUser.getPhoneNumber2());
			preparedStatement.setString(10, systemUser.getAddress());
			preparedStatement.setString(11, systemUser.getAddress2());
			preparedStatement.setString(12, systemUser.getEmail());
			preparedStatement.setString(13, systemUser.getEmail2());
			preparedStatement.setTimestamp(14, systemUser.getCreatedDate());
			preparedStatement.setTimestamp(15, systemUser.getLastUpdate());
			preparedStatement.setLong(16, systemUser.getSystemUserCreateID());
			preparedStatement.setLong(17, systemUser.getSystemUserActiveID());
			preparedStatement.setLong(18, systemUser.getSystemUserID());
			
			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				return rs.getLong(1);
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public List<SystemUser> getSystemUser() {
		String query = "SELECT * FROM `SystemUser` WHERE UserStatus = 'active'";
		List<SystemUser> systemUser = new ArrayList<SystemUser>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			//connection = dataSource.getConnection();
			connection = getConnection();
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				systemUser.add(getSystemUserResultSet(rs));
				//System.out.println(getSystemUserResultSet(rs).toString());
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return systemUser;
	}

	@Override
	public List<SystemUser> getSystemUserByID(long systemUserID) {

		String query = "SELECT * FROM `SystemUser` WHERE SystemUserID LIKE '%"
				+ systemUserID + "%'";
		List<SystemUser> systemUser = new ArrayList<SystemUser>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				systemUser.add(getSystemUserResultSet(rs));
				//System.out.println(getSystemUserResultSet(rs).toString());
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return systemUser;
	}

	@Override
	public List<SystemUser> getSystemUserByState(String systemUserState) {
		List<SystemUser> resultList = new ArrayList<SystemUser>();
		String query = "SELECT * FROM `SystemUser` WHERE `UserStatus`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setString(1, systemUserState);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				SystemUser systemUser = new SystemUser();
				systemUser.setSystemUserID(rs.getLong("SystemUserID"));
				systemUser.setUserName(rs.getString("UserName"));
				systemUser.setPassWord(rs.getString("Password"));
				systemUser.setIdCardNumber(rs.getString("IDCardNumber"));
				systemUser.setFirstName(rs.getString("FirstName"));
				systemUser.setLastName(rs.getString("LastName"));
				systemUser.setMidName(rs.getString("MidName"));
				systemUser.setPhoneNumber(rs.getString("PhoneNumber"));
				systemUser.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				systemUser.setAddress(rs.getString("Address"));
				systemUser.setAddress2(rs.getString("Address_2"));
				systemUser.setEmail(rs.getString("Email"));
				systemUser.setEmail2(rs.getString("Email_2"));
				systemUser.setCreatedDate(rs.getTimestamp("CreatedDate"));
				systemUser.setLastUpdate(rs.getTimestamp("LastUpdate"));
				systemUser.setSystemUserState(SystemUserState.valueOf(rs
						.getString("UserStatus").toUpperCase()));
				systemUser.setSystemUserCreateID(rs.getLong("CreatedBy"));
				systemUser.setSystemUserActiveID(rs.getLong("ApprovedBy"));

				resultList.add(systemUser);
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	private SystemUser getSystemUserResultSet(ResultSet rs) throws SQLException {
		SystemUser systemUser = new SystemUser();
		systemUser.setSystemUserID(rs.getLong("SystemUserID"));
		systemUser.setUserName(rs.getString("UserName"));
		systemUser.setPassWord(rs.getString("Password"));
		systemUser.setSystemUserState(SystemUserState.valueOf(rs.getString(
				"UserStatus").toUpperCase()));
		systemUser.setIdCardNumber(rs.getString("IDCardNumber"));
		systemUser.setFirstName(rs.getString("FirstName"));
		systemUser.setLastName(rs.getString("LastName"));
		systemUser.setMidName(rs.getString("MidName"));
		systemUser.setPhoneNumber(rs.getString("PhoneNumber"));
		systemUser.setPhoneNumber2(rs.getString("PhoneNumber_2"));
		systemUser.setAddress(rs.getString("Address"));
		systemUser.setAddress2(rs.getString("Address_2"));
		systemUser.setEmail(rs.getString("Email"));
		systemUser.setEmail2(rs.getString("Email_2"));
		systemUser.setCreatedDate(rs.getTimestamp("CreatedDate"));
		systemUser.setLastUpdate(rs.getTimestamp("LastUpdate"));
		systemUser.setSystemUserCreateID(rs.getLong("CreatedBy"));
		systemUser.setSystemUserActiveID(rs.getLong("ApprovedBy"));

		return systemUser;
	}



	@Override
	public List<SystemUser> getSystemUserByFirstName(String firstName) {
		List<SystemUser> resultList = new ArrayList<SystemUser>();
		String query = "SELECT * FROM `SystemUser` WHERE `FirstName`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setString(1, firstName);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				SystemUser systemUser = new SystemUser();
				systemUser.setSystemUserID(rs.getLong("SystemUserID"));
				systemUser.setUserName(rs.getString("UserName"));
				systemUser.setPassWord(rs.getString("Password"));
				systemUser.setIdCardNumber(rs.getString("IDCardNumber"));
				systemUser.setFirstName(rs.getString("FirstName"));
				systemUser.setLastName(rs.getString("LastName"));
				systemUser.setMidName(rs.getString("MidName"));
				systemUser.setPhoneNumber(rs.getString("PhoneNumber"));
				systemUser.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				systemUser.setAddress(rs.getString("Address"));
				systemUser.setAddress2(rs.getString("Address_2"));
				systemUser.setEmail(rs.getString("Email"));
				systemUser.setEmail2(rs.getString("Email_2"));
				systemUser.setCreatedDate(rs.getTimestamp("CreatedDate"));
				systemUser.setLastUpdate(rs.getTimestamp("LastUpdate"));
				systemUser.setSystemUserState(SystemUserState.valueOf(rs
						.getString("UserStatus").toUpperCase()));
				systemUser.setSystemUserCreateID(rs.getLong("CreatedBy"));
				systemUser.setSystemUserActiveID(rs.getLong("ApprovedBy"));

				resultList.add(systemUser);
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<SystemUser> getSystemUserByPhoneNumber(String phoneNumber) {
		List<SystemUser> resultList = new ArrayList<SystemUser>();
		String query = "SELECT * FROM `SystemUser` WHERE PhoneNumber LIKE '%"
				+ phoneNumber + "%' OR PhoneNumber_2 LIKE '%" + phoneNumber
				+ "%'";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				SystemUser systemUser = new SystemUser();
				systemUser.setSystemUserID(rs.getLong("SystemUserID"));
				systemUser.setUserName(rs.getString("UserName"));
				systemUser.setPassWord(rs.getString("Password"));
				systemUser.setIdCardNumber(rs.getString("IDCardNumber"));
				systemUser.setFirstName(rs.getString("FirstName"));
				systemUser.setLastName(rs.getString("LastName"));
				systemUser.setMidName(rs.getString("MidName"));
				systemUser.setPhoneNumber(rs.getString("PhoneNumber"));
				systemUser.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				systemUser.setAddress(rs.getString("Address"));
				systemUser.setAddress2(rs.getString("Address_2"));
				systemUser.setEmail(rs.getString("Email"));
				systemUser.setEmail2(rs.getString("Email_2"));
				systemUser.setCreatedDate(rs.getTimestamp("CreatedDate"));
				systemUser.setLastUpdate(rs.getTimestamp("LastUpdate"));
				systemUser.setSystemUserState(SystemUserState.valueOf(rs
						.getString("UserStatus").toUpperCase()));
				systemUser.setSystemUserCreateID(rs.getLong("CreatedBy"));
				systemUser.setSystemUserActiveID(rs.getLong("ApprovedBy"));

				resultList.add(systemUser);
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<SystemUser> getSystemUserByAddress(String address) {
		List<SystemUser> resultList = new ArrayList<SystemUser>();
		String query = "SELECT * FROM `SystemUser` WHERE `Address` LIKE '%"
				+ address + "%' OR `Address_2` LIKE '%" + address
				+ "%'";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				SystemUser systemUser = new SystemUser();
				systemUser.setSystemUserID(rs.getLong("SystemUserID"));
				systemUser.setUserName(rs.getString("UserName"));
				systemUser.setPassWord(rs.getString("Password"));
				systemUser.setIdCardNumber(rs.getString("IDCardNumber"));
				systemUser.setFirstName(rs.getString("FirstName"));
				systemUser.setLastName(rs.getString("LastName"));
				systemUser.setMidName(rs.getString("MidName"));
				systemUser.setPhoneNumber(rs.getString("PhoneNumber"));
				systemUser.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				systemUser.setAddress(rs.getString("Address"));
				systemUser.setAddress2(rs.getString("Address_2"));
				systemUser.setEmail(rs.getString("Email"));
				systemUser.setEmail2(rs.getString("Email_2"));
				systemUser.setCreatedDate(rs.getTimestamp("CreatedDate"));
				systemUser.setLastUpdate(rs.getTimestamp("LastUpdate"));
				systemUser.setSystemUserState(SystemUserState.valueOf(rs
						.getString("UserStatus").toUpperCase()));
				systemUser.setSystemUserCreateID(rs.getLong("CreatedBy"));
				systemUser.setSystemUserActiveID(rs.getLong("ApprovedBy"));

				resultList.add(systemUser);
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}
	@Override
	public List<SystemUser> getSystemUserByEmail(String email) {
		List<SystemUser> resultList = new ArrayList<SystemUser>();
		String query = "SELECT * FROM `SystemUser` WHERE `Email` LIKE '%"
				+ email + "%' OR `Email_2` LIKE '%" + email
				+ "%'";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				SystemUser systemUser = new SystemUser();
				systemUser.setSystemUserID(rs.getLong("SystemUserID"));
				systemUser.setUserName(rs.getString("UserName"));
				systemUser.setPassWord(rs.getString("Password"));
				systemUser.setIdCardNumber(rs.getString("IDCardNumber"));
				systemUser.setFirstName(rs.getString("FirstName"));
				systemUser.setLastName(rs.getString("LastName"));
				systemUser.setMidName(rs.getString("MidName"));
				systemUser.setPhoneNumber(rs.getString("PhoneNumber"));
				systemUser.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				systemUser.setAddress(rs.getString("Address"));
				systemUser.setAddress2(rs.getString("Address_2"));
				systemUser.setEmail(rs.getString("Email"));
				systemUser.setEmail2(rs.getString("Email_2"));
				systemUser.setCreatedDate(rs.getTimestamp("CreatedDate"));
				systemUser.setLastUpdate(rs.getTimestamp("LastUpdate"));
				systemUser.setSystemUserState(SystemUserState.valueOf(rs
						.getString("UserStatus").toUpperCase()));
				systemUser.setSystemUserCreateID(rs.getLong("CreatedBy"));
				systemUser.setSystemUserActiveID(rs.getLong("ApprovedBy"));

				resultList.add(systemUser);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}


	@Override
	public long createRecordLogin(SystemRecordLogin systemRecordLogin) {
		String query = "INSERT INTO `recordlogin` (`RecordLoginID`,`BeginTime`,`SystemUserID`,`EndTime`) VALUES (?,?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			//connection = getConnection();
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setLong(1, systemRecordLogin.getRecordLoginID());
			preparedStatement.setTimestamp(2, systemRecordLogin.getBeginTime());
			preparedStatement.setLong(3, systemRecordLogin.getSystemUserID());
			preparedStatement.setTimestamp(4, systemRecordLogin.getEndTime());

			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				return rs.getLong(1);
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public long getSystemUserIDByUserName(String userName) {
		String query = "SELECT `SystemUserID` FROM `SystemUser` WHERE `UserName` = '"
				+ userName + "'";
		long systemUserID = 0L;

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				systemUserID = rs.getLong(1);
				//System.out.println(rs.getLong(1));
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return systemUserID;
	}
	private long getRoleResultSet(ResultSet rs) throws SQLException {
		long role = rs.getLong("RoleID");
		return role;
	}
	@Override
	public List<Long> getRoleByUserName(String username) {
		String query = "SELECT `RoleID` FROM `systemuser`, `systemuser_has_role` ";
		 query += "WHERE systemuser.SystemUserID = systemuser_has_role.SystemUserID ";
		 query += "AND systemuser.UserName = '" + username +"'";
		List<Long> listRole = new ArrayList<Long>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			//connection = getConnection();
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				listRole.add(getRoleResultSet(rs));
				//System.out.println(getRoleResultSet(rs));
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return listRole;
	}

	@Override
	public SystemUser get1SystemUserByID(long systemUserID) {
		String query = "SELECT * FROM `SystemUser` WHERE SystemUserID=?";
		SystemUser systemUser = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, systemUserID);
			rs = preparedStatement.executeQuery();
			if(rs.next()){
				systemUser = getSystemUserResultSet(rs);	
			}
			rs.close();

		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
					logger);
		}
		return systemUser;
	}

	@Override
	public boolean changeSystemUserStatetoActive(SystemUser systemUser) {
		String query = "UPDATE `SystemUser` "
				+ " SET  `UserStatus`='active'"
				+ "WHERE `SystemUserID`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, systemUser.getSystemUserID());
			preparedStatement.execute();
			connection.commit();
			return true;
			
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				logger.error(e1);
			}
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
		return false;
	}

	@Override
	public boolean changeSystemUserStatetoDisable(SystemUser systemUser) {
		String query = "UPDATE `SystemUser` "
				+ " SET  `UserStatus`='disable'"
				+ "WHERE `SystemUserID`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, systemUser.getSystemUserID());
			preparedStatement.execute();
			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				logger.error(e1);
			}
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
		return false;
	}

	
	private SystemRecordLogin getRecordLoginResultSet(ResultSet rs) throws SQLException {
		SystemRecordLogin systemRecord = new SystemRecordLogin();
		systemRecord.setRecordLoginID(rs.getLong("RecordLoginID"));
		systemRecord.setSystemUserID(rs.getLong("SystemUserID"));
		systemRecord.setUserName(rs.getString("UserName"));
		systemRecord.setFirstName(rs.getString("FirstName"));
		systemRecord.setLastName(rs.getString("LastName"));
		systemRecord.setMidName(rs.getString("MidName"));
		systemRecord.setBeginTime(rs.getTimestamp("BeginTime"));		
		systemRecord.setEndTime(rs.getTimestamp("EndTime"));		

		return systemRecord;
	}
	
	@Override
	public List<SystemRecordLogin> getRecordLogin() {
		String query = "SELECT * FROM `recordlogin`,`systemuser` ";
		query += "WHERE recordlogin.SystemUserID = systemuser.SystemUserID ";
		query += "ORDER BY EndTime DESC";
		List<SystemRecordLogin> record = new ArrayList<SystemRecordLogin>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			//connection = getConnection();
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				record.add(getRecordLoginResultSet(rs));
				//System.out.println(getRecordLoginResultSet(rs).toString());
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return record;
	}

	@Override
	public List<SystemRecordLogin> getRecordLoginByID(long systemUserID, Timestamp fromDate, Timestamp toDate) {
		String query = "SELECT * FROM `recordlogin`,`systemuser` "; 
		query += "WHERE recordlogin.SystemUserID = systemuser.SystemUserID ";
		query += "AND recordlogin.SystemUserID = '" + systemUserID + "' ";
		query += "AND EndTime between '"+ fromDate +"' AND '"+ toDate +"'";
		query += "ORDER BY EndTime DESC";
		List<SystemRecordLogin> record = new ArrayList<SystemRecordLogin>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			//connection = dataSource.getConnection();
			connection = getConnection();
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				record.add(getRecordLoginResultSet(rs));
				//System.out.println(getRecordLoginResultSet(rs).toString());
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return record;
	}
	
	@Override
	public List<SystemRecordLogin> getRecordLoginByDate(Timestamp from,
			Timestamp to) {
		String query = "SELECT * FROM `recordlogin`,`systemuser` ";
		query += "WHERE recordlogin.SystemUserID = systemuser.SystemUserID "; 
		query += "AND EndTime between '"+ from +"' AND '"+ to +"'";
		List<SystemRecordLogin> record = new ArrayList<SystemRecordLogin>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			//connection = getConnection();
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				record.add(getRecordLoginResultSet(rs));
				//System.out.println(getRecordLoginResultSet(rs).toString());
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return record;
	}

	@Override
	public List<SystemUser> getSystemUserByIDCardNumber(
			String systemUserIDCardNumber) {


		String query = "SELECT * FROM `SystemUser` WHERE IDCardNumber LIKE '%"
				+ systemUserIDCardNumber + "%'";
		List<SystemUser> systemUser = new ArrayList<SystemUser>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				systemUser.add(getSystemUserResultSet(rs));
				//System.out.println(getSystemUserResultSet(rs).toString());
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return systemUser;
		// TODO Auto-generated method stub
	}

	@Override
	public void updateApprovedByID(SystemUser systemUser, long adminID) {
		// TODO Auto-generated method stub
		String query = "UPDATE `SystemUser` "
				+ " SET  `ApprovedBy`=?"
				+ " WHERE `SystemUserID`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, adminID);
			preparedStatement.setLong(2, systemUser.getSystemUserID());
			preparedStatement.execute();
			connection.commit();
			
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				logger.error(e1);
			}
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}		
		
	}



}
